<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Single Sign On Setup File</title>
</head>

<!--- Single Sign On Message File --->

<cfquery name="qryGetPeopleToUpdate" datasource="RosettaStone">
	SELECT RosettaID, UserAccount, UserAccountNM, WFC_PersonID, Null as Pass, Null as Updated, AuthTypeID,
							CASE ISNULL(P.Nickname, '')
								  WHEN '' THEN RTRIM(P.FirstName) + ' ' + RTRIM(P.LastName)
								  ELSE P.Nickname + ' ' + RTRIM(P.LastName)
							END AS FullName,
		
							CASE Left(UserAccount, 4)
								  WHEN 'ANMC' THEN Right(UserAccount, len(UserAccount)-4) + 'anmc.org'
								  ELSE UserAccount + '@southcentralfoundation.com'
							END AS [EMail]
	
	FROM RosettaStone R
		INNER JOIN SCFVM18.WFCSuite.dbo.tPersons P ON R.HRProd_PersonIDNo = P.PersonIdNo AND P.PersonToEffectDate > GETDATE()
		INNER JOIN SCFVM18.WFCSuite.dbo.UserAccount A ON R.WFC_PersonID = A.PersonId 
		INNER JOIN SCFVM18.WFCSuite.dbo.PRSNAuthTypeMM T ON R.WFC_PersonID = T.PersonId 
	WHERE Disabled = 0 and WFC_PersonID is not null and left(UserAccountNM,2) <> 'ZZ' AND RosettaID not in (
							Select RosettaID from Rosetta.dbo.RosettaStone
							Where WFC_PersonID in (
								Select WFC_PersonID
								From Rosetta.dbo.rosettaStone R	
								where WFC_PersonID is not null and disabled = 0
								Group by WFC_PersonID
								Having Count(WFC_PersonID)>1
								)	And left(UserAccount,4) = 'ANMC'
							)
	Order by Lastname
</cfquery>

<!---cfdump var="#qryGetPeopleToUpdate#"--->
<cfoutput>
<body>

<!--- Section #2 Execute Update Logic --->

<!---cfquery name="qryUpdateExpiredAccounts" datasource="EPAR">
	Update SCFVM18.WFCSuite.dbo.UserAccount 
		Set UserAccountNM = 'ZZ' + UserAccountNM
		Where PersonID In (
			Select PersonID
			From PERSONSTATUSMM 
			WHERE EMPLOYMENTSTATID IN (2, 3) and ExpirationDTM > getDate() 
			)
</cfquery--->


<cfset arrBlocked = ArrayNew(1)>
<cfset intIndex = 0>

<cfloop query="qryGetPeopleToUpdate">
		<cfquery name="qryGetPersonID" datasource="WFCDB">
			Select PersonID 
			From UserAccount
			Where UserAccountNM = '#UserAccount#' And PersonID <> #WFC_PersonID#			
		</cfquery>
		
		<cfif left(UserAccount, 4) EQ 'ANMC'>	
			<!---Update UserAccountNM ONLY --->			
			<cfif qryGetPersonID.recordcount GT 0>
				CONFLICT for ANMC User Account - #UserAccountNM#. New name #UserAccount# BLOCKED.<BR />
				<cfset intIndex = intIndex +1>
				<cfset arrBlocked[intIndex] = {UserAccount="#UserAccount#",UserAccountNM="#UserAccountNM#",WFC_PersonID=#WFC_PersonID#}>
			<cfelse>
				ANMC User Account - #UserAccountNM# changed to #UserAccount#<BR />
				<cfquery name="qryUpdateAccountNM" datasource="EPAR">
					Update SCFVM18.WFCSuite.dbo.UserAccount
						Set UserAccountNM = '#UserAccount#'
						Where PersonID = #WFC_PersonID#
				</cfquery>
			</cfif>
			
		<cfelseif UserAccount NEQ UserAccountNM>
			<!--- Update UserAccountNM AND change Auth Type to Windows --->
			<cfif qryGetPersonID.recordcount GT 0>
				CONFLICT for Inequality - NT=#UserAccount# ... Kron=#UserAccountNM# ... New name #UserAccount# BLOCKED.<BR />
				<cfset intIndex = intIndex +1>
				<cfset arrBlocked[intIndex] = {UserAccount="#UserAccount#",UserAccountNM="#UserAccountNM#",WFC_PersonID=#WFC_PersonID#}>
			<cfelse>
				Inequality - NT=#UserAccount# ... Kron=#UserAccountNM# <BR />
				<cfquery name="qryUpdateAccountNM" datasource="EPAR">
					Update SCFVM18.WFCSuite.dbo.UserAccount
						Set UserAccountNM = '#UserAccount#'
						Where PersonID = #WFC_PersonID#
				</cfquery>
				<cfquery name="qryUpdateAuthType" datasource="EPAR">
					Update SCFVM18.WFCSuite.dbo.PRSNAuthTypeMM
						Set AuthTypeID = 2
						Where PersonID = #WFC_PersonID# 
				</cfquery>
			</cfif>
	
		<cfelseif UserAccount EQ UserAccountNM>
			<!--- change Auth Type to Windows --->
			Equality - NT=#UserAccount# ... Kron=#UserAccountNM# <BR />
			<cfquery name="qryUpdateAuthType" datasource="EPAR">
				Update SCFVM18.WFCSuite.dbo.PRSNAuthTypeMM
					Set AuthTypeID = 2
					Where PersonID = #WFC_PersonID# 
			</cfquery>

		<cfelse>
		UNEXPECTED CRITERIA
		
		</cfif>
	
</cfloop>

<cfdump var="#arrBlocked#">

<cfloop from="1" to="5" index="intX">
Conflict Pass ###intX#<BR />
	<cfloop from="#ArrayLen(arrBlocked)#" to="1" step="-1" index="intY">
		<cfquery name="qryGetPersonID" datasource="EPAR">
			Select PersonID 
			From SCFVM18.WFCSuite.dbo.UserAccount
			Where UserAccountNM = '#arrBlocked[intY].UserAccount#' And PersonID <> #arrBlocked[intY].WFC_PersonID#			
		</cfquery>		
			
		<!--- Update UserAccountNM AND change Auth Type to Windows --->
		<cfif qryGetPersonID.recordcount GT 0>
			CONFLICT for Inequality - NT=#arrBlocked[intY].UserAccount# ... Kron=#arrBlocked[intY].UserAccountNM# ... New name #arrBlocked[intY].UserAccount# BLOCKED.<BR />
		<cfelse>
			Inequality - NT=#arrBlocked[intY].UserAccount# ... Kron=#arrBlocked[intY].UserAccountNM# NOT BLOCKED ... Updating<BR />
			<cfquery name="qryUpdateAccountNM" datasource="EPAR">
				Update SCFVM18.WFCSuite.dbo.UserAccount
					Set UserAccountNM = '#arrBlocked[intY].UserAccount#'
					Where PersonID = #arrBlocked[intY].WFC_PersonID#
			</cfquery>
			<cfquery name="qryUpdateAuthType" datasource="EPAR">
				Update SCFVM18.WFCSuite.dbo.PRSNAuthTypeMM
					Set AuthTypeID = 2
					Where PersonID = #arrBlocked[intY].WFC_PersonID# 
			</cfquery>
			<cfset Temp =  ArrayDeleteAt(arrBlocked, intY)>
		</cfif>
		
	</cfloop>
	
	<cfdump var="#arrBlocked#">
	
</cfloop>

</body>
</cfoutput>
</html>
